package com.jintoufs.util.poi;

import com.jintoufs.util.date.DateOperator;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.cosmos.modules.exception.ServiceException;

import java.util.Date;

/**
 * Created by Administrator on 2015/7/1.
 */
public class ExcelUtils {
    public static String getCellStrValue(Cell cell){
        return getCellStrValue(cell,null);
    }
    public static String getCellStrValue(Cell cell,Class type) {
        if(cell==null) return null;
        String ret;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                ret = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                ret = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                ret = null;
                break;
            case Cell.CELL_TYPE_FORMULA:
                Workbook wb = cell.getSheet().getWorkbook();
                CreationHelper crateHelper = wb.getCreationHelper();
                FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
                try{
                    ret = getCellStrValue(evaluator.evaluateInCell(cell),type);
                }catch (Exception e){
                    throw new ServiceException(cell.getSheet().getSheetName()+(cell.getRow().getRowNum()+1)+"行"+(cell.getColumnIndex()+1)+"列，公式解析错误："+e.getMessage());
                }
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (PoiDateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    ret = DateOperator.formatDate(theDate);
                } else if (type!=null&&type.equals(Date.class)) {
                    double value = cell.getNumericCellValue();
                    Date theDate = org.apache.poi.ss.usermodel.DateUtil
                            .getJavaDate(value);
                    ret = DateOperator.formatDate(theDate);
                } else {
                    ret = NumberToTextConverter.toText(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                ret = cell.getRichStringCellValue().getString();
                break;
            default:
                ret = null;
        }

        return ret==null?null:ret.trim(); //有必要自行trim
    }
}
